--USE scm_main;
if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_schedule_update_by_date')
          and type in ('P','PC'))
DROP PROCEDURE bd_proc_schedule_update_by_date
GO
CREATE PROCEDURE bd_proc_schedule_update_by_date
	@user_serial_str varchar(max),
	@year_month_day varchar(50),
	@ban_ci varchar(50),
	@ip varchar(50),
	@operator varchar(50)
as
BEGIN

-- 关闭打印受影响行数
SET NOCOUNT ON;


-- 定义返回受影响行数结果
	declare @result int = 0;


	--  异常扑捉机制
		BEGIN TRY


			create table #user_serial(
				id int identity,
				user_no varchar(50) COLLATE Chinese_PRC_CI_AS,
				user_name varchar(50) COLLATE Chinese_PRC_CI_AS,
				user_serial varchar(50) COLLATE Chinese_PRC_CI_AS,
				dept_name varchar(50)	COLLATE Chinese_PRC_CI_AS,
				dept_serial varchar(50) COLLATE Chinese_PRC_CI_AS
			)

      --创建 返回不满足排班的人的原因
      create table #return_table(
        user_serial VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
        user_no VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
        user_name VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
        dept_name VARCHAR(200) COLLATE Chinese_PRC_CI_AS,
        error_msg VARCHAR(200) COLLATE Chinese_PRC_CI_AS
      )

			create table #shift_shid_info(
				id int identity,
				shift_id int,
				shift_name varchar(200)	COLLATE Chinese_PRC_CI_AS,
				shift_date date,
				shift_begin int,
				shift_end int,
				shift_sign_begin int,
				shift_sign_end int,
				shift_late int,
				shift_early int,
				shift_duty_on_delay int,
				shift_duty_off_delay int,
				shift_workday int,
				shift_workday_hours int,
				is_enjoyment_subsidized int,
				middle_shift_start_time int,
				middle_shift_end_time int,
				night_shift_start_time int,
				night_shift_end_time int,
				repast_start_time int,
				repast_end_time int,
				repast_start_time2 int,
				repast_end_time2 int,
				shift_is_rest int
			)
			
			create table #data(
				id int identity,
				user_no varchar(50)	COLLATE Chinese_PRC_CI_AS,
				user_name varchar(50)	COLLATE Chinese_PRC_CI_AS,
				user_serial varchar(50) COLLATE Chinese_PRC_CI_AS,
				dept_name varchar(50)	COLLATE Chinese_PRC_CI_AS,
				dept_serial varchar(50) COLLATE Chinese_PRC_CI_AS,
				shift_id int,
				shift_name varchar(200)	COLLATE Chinese_PRC_CI_AS,
				shift_date date,
				shift_begin int,
				shift_end int,
				shift_sign_begin int,
				shift_sign_end int,
				shift_late int,
				shift_early int,
				shift_duty_on_delay int,
				shift_duty_off_delay int,
				shift_workday int,
				shift_workday_hours int,
				is_enjoyment_subsidized int,
				middle_shift_start_time int,
				middle_shift_end_time int,
				night_shift_start_time int,
				night_shift_end_time int,
				repast_start_time int,
				repast_end_time int,
				repast_start_time2 int,
				repast_end_time2 int,
				shift_is_rest int
			)

		-- 开启事务
			BEGIN TRAN
				
				-- 拆分用户编号
				insert into #user_serial(user_serial)
				select * from string_split(@user_serial_str,',');


				-- 更新用户工号、名称、部门编号
				update a set a.user_no = isnull(b.user_no,''), a.user_name = isnull(b.user_lname,''), a.dept_serial = isnull(b.user_dep,'')
				from #user_serial a 
				inner join dt_user b on a.user_serial = b.user_serial

        -- 更新部门名称
        update a set a.dept_name = isnull(b.dep_name,'')
        from #user_serial a
        inner join dt_dep b on a.dept_serial = b.dep_serial

				-- 查询班次保存临时表中(这里普通班次默认都是工作日)
				declare @ban_ci_name varchar(50);
				select @ban_ci_name = concat('[A]',isnull(bh,''),'|',isnull(mc,'')) FROM zt_banci t WHERE bh = @ban_ci

				-- 获取排班信息
				insert into #shift_shid_info(shift_id,shift_name,shift_date,shift_begin,shift_end,shift_sign_begin,shift_sign_end,shift_late,shift_early,shift_duty_on_delay,shift_duty_off_delay,shift_workday,shift_workday_hours,is_enjoyment_subsidized,middle_shift_start_time,middle_shift_end_time,night_shift_start_time,night_shift_end_time,repast_start_time,repast_end_time,repast_start_time2,repast_end_time2)
				select a.id,@ban_ci_name,@year_month_day,isnull(b.sbsj,0),isnull(b.xbsj,0),isnull(b.sbks,0),isnull(b.xbjs,0),isnull(b.cdsj,0),isnull(b.ztsj,0),isnull(b.bjcd,0),isnull(b.bjzt,0),isnull(b.alld,0),isnull(b.allt,0),isnull(b.is_enjoyment_subsidized,0),isnull(b.middle_shift_start_time,0),isnull(b.middle_shift_end_time,0),isnull(b.night_shift_start_time,0),isnull(b.night_shift_end_time,0),isnull(repast_start_time,0),isnull(b.repast_end_time,0),isnull(repast_start_time2,0),isnull(b.repast_end_time2,0)
				from zt_banci a
				inner join zt_shid b on a.bh = b.parent
				where a.bh = @ban_ci and b.lx = 0	--上下班的班次信息


				-- 封装基础数据
				insert into #data(user_no,user_name,user_serial,dept_name,dept_serial,shift_id,shift_name,shift_date,shift_begin,shift_end,shift_sign_begin,shift_sign_end,shift_late,shift_early,shift_duty_on_delay,shift_duty_off_delay,shift_workday,shift_workday_hours,is_enjoyment_subsidized,middle_shift_start_time,middle_shift_end_time,night_shift_start_time,night_shift_end_time,repast_start_time,repast_end_time,repast_start_time2,repast_end_time2)
				select a.user_no,a.user_name,a.user_serial,a.dept_name,a.dept_serial,b.shift_id,b.shift_name,b.shift_date,isnull(b.shift_begin,0),isnull(b.shift_end,0),isnull(b.shift_sign_begin,0),isnull(b.shift_sign_end,0),isnull(b.shift_late,0),isnull(b.shift_early,0),isnull(b.shift_duty_on_delay,0),isnull(b.shift_duty_off_delay,0),isnull(b.shift_workday,0),isnull(b.shift_workday_hours,0),isnull(b.is_enjoyment_subsidized,0),isnull(b.middle_shift_start_time,0),isnull(b.middle_shift_end_time,0),isnull(b.night_shift_start_time,0),isnull(b.night_shift_end_time,0),isnull(repast_start_time,0),isnull(b.repast_end_time,0),isnull(repast_start_time2,0),isnull(b.repast_end_time2,0)
				from #user_serial a,#shift_shid_info b


				--declare @shift_is_rest int;
				--set @shift_is_rest = iif(left(isnull(@ban_ci_name,''),3) = '[X]',1,0)
        --update #data set shift_is_rest = iif(left(isnull(shift_name,''),3) = '[X]',1,0)

        -- 更新是否为休息日(一定要在更新kt_paiba表前，删除bd_kq_schedule_detail表数据前)
        update a set a.shift_is_rest = 1,a.shift_name = concat('[X]',substring(a.shift_name,4,len(a.shift_name)))
        from #data a
        inner join (
          -- 两个集合总和的rest大于1 说明一定要是休息日(普通班次)
          select a.user_serial,a.shift_date
          from #data a
          inner join bd_kq_schedule_detail b on a.shift_date = b.shift_date and a.user_serial = b.user_serial
          where (isnull(a.shift_is_rest,0) + isnull(b.shift_is_rest,0)) >= 1
        ) b on a.user_serial = b.user_serial and a.shift_date = b.shift_date



				-- 检测是否为封账状态
				insert into #return_table(user_serial,user_no,user_name,dept_name,error_msg)
				select distinct a.user_serial,a.user_no,a.user_name,a.dept_name,'该日期考勤已经封账,不能排班'
				from #data  a
				inner join bd_kq_accounts b on a.user_serial = b.user_serial and year(a.shift_date) = b.year and month(a.shift_date) = b.month

				-- 删除已经封账的数据(以用户为单位)
				delete a
				from #data a
				inner join #return_table b on a.user_serial = b.user_serial



				-- 有则更新 无则添加
				MERGE bd_kq_schedule_detail b
				USING #data a
				ON a.user_serial = b.user_serial and a.shift_date = b.shift_date
				WHEN MATCHED
					THEN
						UPDATE SET b.shift_id = a.shift_id,b.shift_name = a.shift_name,b.shift_begin = a.shift_begin,b.shift_end = a.shift_end,
						b.shift_sign_begin=a.shift_sign_begin,b.shift_sign_end=a.shift_sign_end,b.shift_late=a.shift_late,b.shift_early=a.shift_early,b.shift_duty_on_delay=a.shift_duty_on_delay,b.shift_duty_off_delay=a.shift_duty_off_delay,b.shift_workday=a.shift_workday,b.shift_workday_hours=a.shift_workday_hours,b.is_enjoyment_subsidized=a.is_enjoyment_subsidized,b.middle_shift_start_time=a.middle_shift_start_time,b.middle_shift_end_time=a.middle_shift_end_time,b.night_shift_start_time=a.night_shift_start_time,b.night_shift_end_time=a.night_shift_end_time,b.repast_start_time=a.repast_start_time,b.repast_end_time=a.repast_end_time,b.repast_start_time2=a.repast_start_time2,b.repast_end_time2=a.repast_end_time2
				WHEN NOT MATCHED
					THEN
						insert (user_serial,shift_id,shift_name,shift_date,shift_begin,shift_end,shift_sign_begin,shift_sign_end,shift_late,shift_early,shift_duty_on_delay,shift_duty_off_delay,shift_workday,shift_workday_hours,is_enjoyment_subsidized,middle_shift_start_time,middle_shift_end_time,night_shift_start_time,night_shift_end_time,repast_start_time,repast_end_time,repast_start_time2,repast_end_time2,shift_is_rest)
						values (a.user_serial,a.shift_id,a.shift_name,a.shift_date,a.shift_begin,a.shift_end,a.shift_sign_begin,a.shift_sign_end,a.shift_late,a.shift_early,a.shift_duty_on_delay,a.shift_duty_off_delay,a.shift_workday,a.shift_workday_hours,a.is_enjoyment_subsidized,a.middle_shift_start_time,a.middle_shift_end_time,a.night_shift_start_time,a.night_shift_end_time,a.repast_start_time,a.repast_end_time,a.repast_start_time2,a.repast_end_time2,0);



				declare @day int = 0;
				set @day = day(@year_month_day);


				declare @sql varchar(max) = '';
				set @sql += 'update a set '+concat('d',@day)+' = b.shift_name ,gly_no = '''+@operator+''' ';
				set @sql += 'from kt_paiba a ';
				set @sql += 'inner join #data b on a.user_serial = b.user_serial ';
				set @sql += 'where a.rq = convert(varchar(7),'''+@year_month_day+''',120) ';

				exec(@sql);
				set @result = @result + @@ROWCOUNT;


				insert into kqfx_rizhi(serial,rq,serial_bz,lx,czbz,clbz,fxbz,mrbc,gly_no,sj)
				select a.user_serial,@year_month_day,1,6,1,0,0,'',@operator,getdate()
				from bd_kq_schedule_detail a
				inner join #user_serial b on a.user_serial = b.user_serial


				insert into  wt_log(log_fun,log_type,log_detail,gly_no,log_time,log_computer,log_ip,regserial)
				select 13,0,concat(@operator,'修改',b.user_name,'','排班'),@operator,getdate(),'',@ip,''
				from bd_kq_schedule_detail a
				inner join #user_serial b on a.user_serial = b.user_serial


        -- 调用考勤计算(排班4)
        EXEC bd_proc_timecard @year_month_day, @user_serial_str, 4
				
		--  提交事务
			COMMIT TRAN

	--  结束异常捕捉
		END TRY

	--  异常处理
		BEGIN CATCH
			DECLARE @errStr VARCHAR(MAX) = ERROR_MESSAGE();
			IF XACT_STATE() <> 0
			BEGIN
			  ROLLBACK TRANSACTION;
			  RAISERROR (@errStr, 16, 1);
			END;
			ROLLBACK TRAN;
		END CATCH

  -- 返回错误信息
	select * from #return_table

--  清除临时表
	drop table #user_serial
	drop table #return_table



-- 打开打印受影响行数
SET NOCOUNT OFF;
END;
